﻿/**********************************天使物流*******************************************
* 功能描述:合同管理-承运合同
* 作者:石锦涛
* 时间:2022-01-08
* 
**********************************天使物流*******************************************/
using Snowflake.Core;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using TianShiProject.EntityFrameworkCore;
using TianShiProject.Models.Common;
using TianShiProject.Models.Contract;
using TianShiProject.Models.Contract.NewOwnerContracts;
using TianShiProject.Models.Contract.NewTransportConts;
using Volo.Abp.EntityFrameworkCore;

namespace TianShiProject.Contract.NewTransportConts
{
    public class NewTranAppRepository : DapperDbContextRepository, INewTranAppRepository
    {
        public NewTranAppRepository(IDbContextProvider<TianShiProjectDbContext> dbContextProvider) : base(dbContextProvider)
        {
        }
        /// <summary>
        /// 显示承运合同
        /// </summary>
        /// <returns></returns>
        public async Task<PageResponse<NewTransportContDtos>> GetNewTransport(string telet, string danwei, string principal, string operators, string dtime, int? state, int pageindex = 1, int pagesize = 5)
        {
            PageResponse<NewTransportContDtos> dos = new PageResponse<NewTransportContDtos>();
            try
            {
                var Sql = "select a.Id as GId,a.TheCarContractNOc,a.TheContractTitlec,a.TheCarrierUnitc,a.TheCarrierHeadc,a.Linec,a.TonsOfFreightc,a.ConditionTonnagec,a.CharterValuec,a.Agentc,SUBSTRING(CONVERT(Nvarchar(50), a.SignedTimec), 1, 10) as SignedTimec,a.TheContractAmountc,a.ProjectDescriptioc,a.ChangeTheTermsc,a.ContractTheTextc,a.ContractStatesc,a.TheApproverc,a.IsDelete,b.LineName from NewTransportCont a join Line b on a.Linec = b.Id where a.IsDelete = 0 ";

                if (!string.IsNullOrWhiteSpace(telet))
                {
                    Sql += $" and a.TheContractTitlec like '%{telet}%'";
                }
                if (!string.IsNullOrWhiteSpace(danwei))
                {
                    Sql += $" and a.TheCarrierUnitc like '%{danwei}%'";
                }
                if (!string.IsNullOrWhiteSpace(principal))
                {
                    Sql += $" and a.TheCarrierHeadc like '%{principal}%'";
                }
                if (!string.IsNullOrWhiteSpace(operators))
                {
                    Sql += $" and a.Agentc like '%{operators}%'";
                }
                if (dtime != null)
                {
                    Sql += $" and SignedTimec ='{dtime}'";
                }
                if (state > 0)
                {
                    Sql += $" and a.ContractStatesc='{state}'";
                }
                var list = await this.QueryAsync<NewTransportContDtos>(Sql, null);

                dos.PageTotal = list.Count();

                Sql += $" order by a.Id offset ({pageindex}-1)*{pagesize} rows fetch next {pagesize} rows only";
                var query = await this.QueryAsync<NewTransportContDtos>(Sql, null);
                dos.PageData = query;
                return dos;



            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 删除承运合同
        /// </summary>
        /// <returns></returns>
        public async Task<PageResponse<NewTransportCont>> DeleteNewTransport(int Id)
        {
            try
            {
                PageResponse<NewTransportCont> doc = new PageResponse<NewTransportCont>();
                var sql = $"update  NewTransportCont set IsDelete=1 where Id= {Id} ";
                doc.Code = await ExecuteAsync(sql,null);
                return doc;
            }
            catch (Exception)
            {

                throw;
            }
        }

        /// <summary>
        /// 修改承运数据
        /// </summary>
        /// <param name="newOwner"></param>
        /// <returns></returns>
        public async Task<PageResponse<int>> UpdateNewTransport(NewTransportContDtos newOwner)
        {
            try
            {
                PageResponse<int> dos = new PageResponse<int>();

                var sql = $"update NewTransportCont set TheCarContractNOc='{newOwner.TheCarContractNOc}',TheContractTitlec='{newOwner.TheContractTitlec}',TheCarrierUnitc='{newOwner.TheCarrierUnitc}',TheCarrierHeadc='{newOwner.TheCarrierHeadc}',Linec='{newOwner.Linec}',TonsOfFreightc='{newOwner.TonsOfFreightc}',ConditionTonnagec='{newOwner.ConditionTonnagec}',CharterValuec ='{newOwner.CharterValuec}',Agentc ='{newOwner.Agentc}',SignedTimec ='{newOwner.SignedTimec.ToString("yyyy/MM/dd")}',TheContractAmountc ='{newOwner.TheContractAmountc}',ProjectDescriptioc ='{newOwner.ProjectDescriptioc}',ChangeTheTermsc ='{newOwner.ChangeTheTermsc}',ContractTheTextc ='{newOwner.ContractTheTextc}',ContractStatesc ='{newOwner.ContractStatesc}',TheApproverc ='{newOwner.TheApproverc}',IsDelete = '{newOwner.IsDelete}' where Id = '{newOwner.GId}'";
                dos.Code = await ExecuteAsync(sql, null);
                return dos;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 添加承运合同
        /// </summary>
        /// <param name="newOwner"></param>
        /// <returns></returns>

        public async Task<PageResponse<int>> PostNewTransport(NewTransportCont newOwner)
        {
            try
            {
                PageResponse<int> dos = new PageResponse<int>();

                //雪花算法
                var worker = new IdWorker(1, 1);
                var id = worker.NextId().ToString();

                var sql = $"insert NewTransportCont values('{id}','{newOwner.TheContractTitlec}','{newOwner.TheCarrierUnitc}','{newOwner.TheCarrierHeadc}','{newOwner.Linec}','{newOwner.TonsOfFreightc}','{newOwner.ConditionTonnagec}','{newOwner.CharterValuec}','{newOwner.Agentc}','{newOwner.SignedTimec.ToString("yyyy/MM/dd")}','{newOwner.TheContractAmountc}','{newOwner.ProjectDescriptioc}','{newOwner.ChangeTheTermsc}','{newOwner.ContractTheTextc}','{newOwner.ContractStatesc}', '{newOwner.TheApproverc}', '{newOwner.IsDelete}')";
                dos.Code = await ExecuteAsync(sql, null);
                return dos;
            }
            catch (Exception)
            {

                throw;
            }
        }
        /// <summary>
        /// 承运批量删除
        /// </summary>
        /// <param name="Id"></param>
        /// <returns></returns>
        public async Task<PageResponse<NewTransportContDto>> DeletEmoreNewTrans(string Id)
        {
            try
            {
                PageResponse<NewTransportContDto> doc = new PageResponse<NewTransportContDto>();
                var ids = Id.Split(',');
                foreach (var item in ids)
                {
                    var shu = Convert.ToInt32(item);
                    var list = await this.QueryAsync<NewTransportContDto>($"select * from NewTransportCont where Id={shu}");
                    if (list != null)
                    {
                        var sql = $"update NewTransportCont set IsDelete=1 where Id={shu} ";
                        doc.Code = await this.ExecuteAsync(sql, null);
                    }
                }
                return doc;
            }
            catch (Exception)
            {

                throw;
            }
        }
    }
}
